How to Add Group Header/Footer in Report
MS Access has provided many different types of tools to create a report. There are many icons for creating a report under the Create menu.
In this How To, I will show how to add a Group Header/Footer on report. In the example below, the auto report was created for Products after I selected the Products table and clicked Report icon from the Create menu.
From the table report above, you can delete all columns accept four fields below. Now we have a new report containing Product Code, Product Name, List Price, and Category fields.
Its texture is juicy and comes in variety of flavors which makes it extremely popular among individuals who needn’t bother with it for free sample of cialis erectile brokenness. One can cialis prices robertrobb.com take any of these medicines through nearest pharmaceutical store. This strong anti-impotency medicine could be used for better erections. sildenafil rx is the drug to be filled. For such people online pharmaceutical store could be better online sildenafil option.
Add Group Header
From previous report, open it in a Design View mode.
- Click Group & Sort icon from the Design A Group, Sort, and Total section will display at the bottom of this report design view.
- Click Add a group button to add the Group Header and Group Footer
In this How To, I want to group the product by its category. After click the Add a group button, we need to select a Category item from the field list as shown in the picture below.
Now a Category Header section is added to report, but no data field on this section yet.
Add Field
To open a Field List window, we need to click on the Add Existing Fields icon next to the Property Sheet icon under the Tools group of the Design menu. Select Category field and drag it to the area under the Category Header section like picture below.
View Report:
The Category will display on top and all products with their categories will be displayed under each category as shown in the picture below.
Add Group Footer and Total
Under the Group, Sort, and Total section,
- Click More► to expand the menu bar.
- Change a without a footer section to with a footer section
Add Subtotal and Grand Total of List Price
- Click on with List Price totaled section
- Select List Price on a Total On drop-down list
- Select Sum on a Type drop-down list (total List Price)
- Check Show Grand Total and Show subtotal in group footer
Add Count of Product on Each Category Header
- Click on with List Price totaled section
- Select Category on the Total On drop-down list
- Select Count on the Type drop-down list (count Category)
- Check Show subtotal in group header
- Add Label box for a =Sum([List Price]) under the Category Footer and name it as List Price Total:
- Add Label box for a =Sum([List Price]) under the Report Footer and name it as Grand Total:
All labels and the total boxes are completed as shown below. The category will be sorted as A-Z (with A on top).
View Group Report
Under the Report View below, there are 4 products under the Baked Goods & Mixes category from the formula that was inserted above. This category has $47.68 on the List Price Total.
The Grand Total price is $713.06 from all products and all categories on the Report Footer section.
Add 2nd Group Header/Footer
We already have the 1st group is Category from the example above. We can add more than one category. In order to add the 2nd category, please follow the steps below.
- Click Add a group button at the bottom of the Group, Sort, and Total section
- Select Discontinued field and drag it to the area under the Discontinued Header section
- Click on the arrow up ▲ to move the Discontinued Header section to top of the Category Header section as we want to group the products by Discontinued product and then by Category. There are many categories under the discontinued/not discontinued category.
View Group Report
Under the Report View below, the Discontinued group is on top the Category group. There are 4 products under three categories (Condiments, Dried Fruit & Nuts, and Jams, preserves) from the discontinued group.
Add a Sort
In the diagram below, you can see there are levels of grouping from Discontinued to Category. Each level will sort in the order from A – Z or small number to large number automatically. However, the Detail section will be sorted automatically by the auto ID that was added into database. You can sort the Detail section by the specific field as you want.
For instant, I want to see the products with the smallest list price on top on each category. So I click on “Add a sort” and leave it sorted by from smallest to largest as shown below.
Report View of Products sorted by smallest to largest List Price under the Discontinued and Category groups.
